MySQL基础语法

注:表名和列名尽量避免使用MySQL关键字

一、常用SQL语句

1.DDL语句

create, alter, drop

# 创建数据库
create database e_mall;

# 删除数据库
drop database e_mall;

# 创建表
create table if not exists `product_order`(
`id` bigint(20) unsigned auto_increment,
`product_id` bigint(20) unsigned not null comment '产品id',
`count` int unsigned not null comment '购买数量',
`amount` decimal(10,2) not null comment '总价格',
`create_time` datetime not null comment '下单时间',
`modified_time` datetime default null comment '修改时间',
primary key (`id`)
)engine=InnoDB default charset=utf8mb4;

# 查看表信息
desc product_order;
# 查看创建语句
show create table product\G

# 删除表: drop
drop table product_order;

# 修改表: alter
# 添加列: add
alter table product_order add column user_id bigint(20) unsigned not null comment '用户id' after id;
# 删除列: drop
alter table product_order drop column user_id;
# 修改列定义: modify
alter table product_order modify amount decimal(10,2) not null comment '总价(元)';
# 修改列定义及列名: change
alter table product_order change count quantity int unsigned not null comment '购买数量';
# 添加普通索引: add index
alter table product_order add index idx_product_id (`product_id`);
# 删除普通索引: drop index
alter table product_order drop index idx_product_id;
# 添加唯一索引
alter table product_order add unique (`order_serial`);
# 修改表名: alter table rename
alter table product_order rename product_orders;
# 跨库修改表名: rename table to
rename table e_mall_2.product to e_mall_0.product;

2.DML语句

insert, update, delete, select

# 插入记录: insert
insert into product_order(product_id, quantity, amount, create_time) values (3, 1, 7388.00, now());
# insert select语句
insert into product_order(product_id, quantity, amount, create_time)
  select id, 2, 2*price, now() from product where id = 1;

# 更新记录: (join) update
update product_order o join product p on o.product_id = p.id
  set o.quantity = 5, amount = 5*p.price where o.id = 1;

# 删除记录: delete
delete from product_order where id = 10;
# 删除表中所有数据: truncate, 与不带where条件的delete效果相同, 需要事务资源少, 速度快
# delete是DML语句, truncate是DDL语句
truncate table product_order;

# 查询记录
select * from product_order where quantity > 0 order by amount desc limit 1, 1;\
# 聚合查询: group by [having], having关键字对分类后的结果再进行过滤
select product_id, count(1) as order_count, sum(quantity) as total_quantity, sum(amount) as total_amount
  from product_order where quantity > 0 group by product_id having total_quantity > 2;

# 表连接查询: join
# 全连接: full join
select * from product full join product_order;
# 查询多表的全连接
select * from product, product_order;
# 子连接: in, not in, =, !=, exists, not exists
# in查询: 索引用在product_order, 与exists的区别在于索引作用的表不同
select * from product_order where product_id in (select id from product);
# exists条件查询: 当能返回结果集则为1, 不能返回结果集则为0; 索引用在product表
select * from product_order where exists (select * from product where product.id = product_order.product_id);

3.DCL语句

grant

# 用户授权
grant select,insert on e_mall.* to 'test_user'@'%' identified by 'Dev!@#123' with grant option;
# 帮助使用: ? Xxx
# 查看所有的show命令
? show

二、数据类型

1.数值类型

整数类型	字节数
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT, INTEGER	4
BIGINT	8
浮点数类型	字节数
FLOAT	4
DOUBLE	8
定点数类型	字节数
DEC(M,D)	M+2
DECIMAL(M,D)	M+2
位类型	字节数
BIT(M)	1~8(M: 1~64)

2.日期类型

类型	字节数	描述
DATETIME	8	YYYY-MM-DD HH:MM:SS
DATE	4	YYYY-MM-DD
TIME	3	HH:MM:SS
TIMESTAMP	4	时间戳

3.字符串类型

类型	字节数	描述
CHAR(M)	M	M: 0~255, 固定长度
VARCHAR(M)		M: 0~65535, 可变长
TINYBLOB		0~255
BLOB		0~65535
MEDIUMBLOB
LONGBLOB
TINYTEXT		0~255
TEXT		0~65535
MEDIUMTEXT
LONGTEXT
BINARY(M)
VARBINARY

4.枚举类型和SET类型

# 创建表
create table if not exists `member` (
`id` int unsigned auto_increment,
`gender` enum('M','F') not null,
`hobby` set('running','movie','music') not null,
primary key (`id`)
)engine=InnoDB default charset=utf8mb4;

# 插入记录: 枚举类型只能从集合中选取单个值, SET类型可以一次选取多个成员
insert into member(gender, hobby) values ('M', 'running,movie');

三、运算符

1.算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/, DIV	除法, 返回商
%, MOD	除法, 返回余数

2.比较运算符

运算符	作用
=	等于
<> 或 !=	不等于
<=>	NULL安全的等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	存在于指定范围
IN	存在于指定集合
IS NULL	为NULL
IS NOT NULL	为NULL
LIKE	通配符匹配
REGEXP 或 RLIKE	正则匹配

3.逻辑运算符

运算符	作用
NOT 或 !	逻辑非
AND 或 &&	逻辑与
OR 或 \	\		逻辑或
XOR	逻辑异或

4.位运算符

运算符	作用
&	位与
\		位或
^	位异或
~	位取反
>>	位右移
<<	位左移

5.运算的优先级

优先级顺序	运算符
1	:=
2	\	\	, OR, XOR
3	&&, AND
4	NOT
5	BETWEEN, CASE, WHEN, THEN, ELSE
6	=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7	\
8	&
9	<<, >>
10	-, +
11	*, /, DIV, %, MOD
12	^
13	-, ~
14	!

四、常用函数

1.字符串函数

函数	功能
ASCII(char)	返回字符的ASCII码值
BIT_LENGTH(str)	返回字符串的比特长度
CONCAT(s1,s2...,sn)	将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)	将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)	分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str) 或 LOWER(str)	返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)	返回字符串str中最左边的x个字符
LENGTH(s)	返回字符串str中的字符数
LTRIM(str)	去掉字符串str左侧的空格
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
POSITION(substr,str)	返回子串substr在字符串str中第一次出现的位置
QUOTE(str)	用反斜杠转义str中的单引号
REPEAT(str,x)	返回字符串str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有的字符串a
REVERSE(str)	返回颠倒字符串str的结果
RIGHT(str,x)	返回字符串str中最右边的x个字符
RTRIM(str)	去掉字符串str尾部的空格
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去除字符串首部和尾部的空格
SUBSTRING(str,x,y)	返回从字符串str的x位置起y个字符长度的字符串
UCASE(str) 或 UPPER(str)	返回将字符串str中所有字符转变为大写后的结果

2.数值函数

函数	功能
ABS(x)	返回x的绝对值
BIN(x)	返回x的二进制(OCT返回八进制，HEX返回十六进制)
CEIL(x) 或 CEILING(x)	返回大于x的最小整数值
EXP(x)	返回值e(自然对数的底)的x次方
FLOOR(x)	返回小于x的最大整数值
GREATEST(x1,x2,...,xn)	返回集合中最大的值
LEAST(x1,x2,...,xn)	返回集合中最小的值
LN(x)	返回x的自然对数
LOG(x,y)	返回x的以y为底的对数
MOD(x,y)	返回x除y的模(余数)
PI()	返回pi的值(圆周率)
RAND()	返回0~1内的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
SIGN(x)	返回代表数字x的符号的值
SQRT(x)	返回一个数的平方根
TRUNCATE(x,y)	返回数字x截短为y位小数的结果

3.日期和时间函数

函数	功能
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW()	返回当前的日期和时间
DATE_ADD(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果
DATE_SUB(date,INTERVAL int keyword)	返回日期date减去间隔时间int的结果
DATE_FORMAT(date,fmt)	返回按fmt格式化日期date值
DATE_DIFF(date1,date2)	返回date1和date2之间的天数
YEAR(date)	返回日期date的年份(1000~9999)
QUARTER(date)	返回date在一年中的季度(1~4)
MONTH(date)	返回date的月份值(1~12)
WEEK(date)	返回日期date为一年中第几周(0~53)
HOUR(time)	返回time的小时值(0~23)
MINUTE(time)	返回time的分钟值(0~59)
DAYOFYEAR(date)	返回date是一年的第几天(1~366)
DAYOFMONTH(date)	返回date是一个月的第几天(1~31)
DAYOFWEEK(date)	返回date所代表的一星期中的第几天(1~7)
MONTHNAME(date)	返回date的月份名
DAYNAME(date)	返回date的星期名
FROM_UNIXTIME(ts,fmt)	返回按fmt格式化UNIX时间戳ts的值
UNIX_TIMESTAMP(date)	返回date的UNIX时间戳

时间日期格式符

格式符	说明
%Y	4位数字表示的年份(2018)
%y	2位数字表示的年份(18)
%M	月名(January, February)
%b	缩写的月名(Jan, Feb)
%m	2位数字表示的月份(01,02,...,12)
%c	数字表示的月份(1,2,...,12)
%D	英文后缀表示的月中的天数(1st, 2nd, 3rd)
%d	2位数字表示的月中的天数(01,02,...,31)
%e	数字形式表示的月中的天数(1,2,...,31)
%H	2位数字24小时制(00,01,...,23)
%h 或 %I	2位数字12小时制(01,...,12)
%k	数字的24小时制(0,1,...,23)
%l	数字的12小时制(1,2,...,12)
%i	2位数字的分(00,01,...,59)
%S 或 %s	2位数字的秒(00,01,...,59)
%T	24小时制时间格式(hh:mm:ss)
%r	12小时制时间格式(hh:mm:ssAM 或 hh:mm:ssPM)

4.聚合函数

函数	功能
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
SUM(col)	返回指定列的所有值之和
MAX(col)	返回指定列的最大值
MIN(col)	返回指定列的最小值
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

5.流程函数

函数	功能
IF(expr,t,f)	如果expr为真, 则返回t, 否则返回f
IFNULL(a,b)	如果a不为空, 则返回a, 否则返回b
CASE WHEN expr1 THEN res1 ... ELSE default END	如果expr1为真, 则返回res1, 否则default
CASE expr WHEN val1 THEN res1 ... ELSE default END	如果expr等于val1, 则返回res1, 否则返回default

6.其他常用函数

函数	功能
LAST_INSERT_ID()	当前线程最后插入记录使用的自增ID值
MD5(str)	返回字符串str的MD5散列后的值
SHA(str)	返回字符串str的SHA散列后的值
PASSWORD(str)	返回字符串str的加密版本
INET_ATON(ip)	返回ip的数字表示
INET_NTOA(num)	返回数字代表的ip地址
DATABASE()	返回当前数据库名
VERSION()	返回MySQL服务器的版本
USER() 或 SYSTEM_USER()	返回当前登陆用户名
CONNECTION_ID()	返回当前客户的连接ID
FOUND_ROWS()	返回最后一个SELECT查询进行检索的总行数
BENCHMARK(count,expr)	将表达式expr重复运行count次

五、视图、存储过程和触发器

1.视图

# 创建视图: create view ... as ...
create or replace view order_view as
  select o.order_serial, o.product_id, p.name as product_name, o.quantity, o.amount
  from product_order o inner join product p on o.product_id = o.id;
# 修改视图: alter view ... as ...
alter view order_view as
  select o.order_serial, o.product_id, p.name as product_name, o.quantity, o.amount
  from product_order o inner join product p on o.product_id = p.id;
# 查询视图: select
select * from order_view;
# 删除视图: drop view
drop view order_view;

2.存储过程

1).创建存储过程示例:

# 存储过程
# 将结束符改为"$$"
DELIMITER $$
# 创建存储过程: 入参: product_id, user_id, 出参: handle_result
CREATE PROCEDURE purchase_product(IN p_product_id BIGINT(20), IN p_user_id BIGINT(20), OUT p_handle_result TINYINT(1))
# 指定用创建者(DEFINER)还是调用者(INVOKER)的许可来执行
SQL SECURITY DEFINER
# 存储过程开始
BEGIN
    # DECLARE声明顺序: 变量, 条件, 光标, 处理程序
    # ----- 定义变量: 定义变量要在定义条件之前
    DECLARE handle_result TINYINT(1) DEFAULT 0;
    # 订单金额
    DECLARE order_amount DECIMAL(10,2) DEFAULT 0.0;
    # ----- 定义条件: 主键冲突, declare cond_name condition for ...
    DECLARE duplicate_key_cond CONDITION FOR sqlstate '23000';
    # ----- 定义条件处理: declare handle_type(CONTINUE:继续, EXIT:退出) handler for ...
    DECLARE CONTINUE HANDLER FOR duplicate_key_cond SET handle_result = -1;
    # 处理SQL异常
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET handle_result = -2;
    # 开始事务
    START TRANSACTION;
    # 查询商品价格并设为订单金额
    SELECT price from product where id = p_product_id INTO order_amount;
    # 插入订单表
    INSERT INTO product_order (order_serial, product_id, user_id, quantity, amount ,create_time)
      VALUES (REPLACE(UUID(),"-",""), p_product_id, p_user_id, 1, order_amount, now());
    # 更新库存
    UPDATE product SET stock = stock - 1 WHERE id = p_product_id and stock > 1;
    # 事务提交或回滚
    IF handle_result < 0 THEN    
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
    # 将处理赋给输出参数p_handle_result
    SET p_handle_result = handle_result;
END
# 流程结束
$$
# 将结束符改为";"
DELIMITER ;

2).常用操作

# 1.调用存储过程
call purchase_product(3,1,@r);
# 查看结果@r
select @r;
# 2.查看存储过程创建语句
show create procedure purchase_product;
# 3.删除存储过程
drop procedure purchase_product;

3.函数(类似存储过程): BEGIN ... END

# 创建函数: 计算所有商品的价值, 只有入参, 因此不需要IN和OUT标识
CREATE FUNCTION get_product_amount(p_status TINYINT(1))
# 定义返回值类型
RETURNS DECIMAL(10,2)
# 定义模式
DETERMINISTIC
READS SQL DATA
# 函数开始
BEGIN
    # 定义总价值变量
    DECLARE v_product_amount DECIMAL(10,2);
    # 定义临时变量v_price, v_stock用于计算
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;
    # ----- 定义光标: DECLARE cursor_name CURSOR FOR select_statement
    DECLARE product_list CURSOR FOR SELECT price, stock FROM product WHERE status = p_status;
    # ----- 定义错误处理器, 出错时关闭光标
    DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE product_list;
    # 商品总价值赋初值
    SET v_product_amount = 0;
    # 开启光标进行计算
    OPEN product_list;
    # LOOP循环处理
    outer:LOOP
        FETCH product_list INTO v_price, v_stock;
            SET v_product_amount = v_product_amount + v_price * v_stock;
    END LOOP outer;
    # 关闭光标
    CLOSE product_list;
    # 返回结果
    RETURN v_product_amount;
END
$$

4.流程控制

# 1.IF语句
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

# 2.CASE语句
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
# 或
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

# 3.LOOP(循环) + LEAVE(类似break: 退出) + ITERATE(类似continue: 直接进入下一轮循环)语句示例:
outer: LOOP
    set @x = @x + 1;
    IF @x = 100 THEN
        # break
        LEAVE outer;
    ELSE
        # continue
        ITERATE outer;
    END IF;
END LOOP outer;

# 4.REPEAT: 条件满足时退出循环
[label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT[label]

# 5.WHILE: 条件满足时执行循环
[label:] WHILE search_condition DO
    statement_list
END WHILE[label]

5.事件调度器: 类似Linux下的crontab

# 创建EVENT
CREATE EVENT uuid_generate_event
    ON SCHEDULE EVERY 10 SECOND
    DO
        INSERT INTO e_mall.uuid_gen(uuid) values (REPLACE(UUID(),'-',''));
# 查看所有EVENT
SHOW EVENTS\G
# 删除EVENT
DROP EVENT uuid_generate_event;

6.触发器

触发器只能创建在永久表(Permanent Table)上, 不能用于临时表(Temporary Table)

# 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
    ON table_name FOR EACH ROW trigger_statement
# trigger_time: 触发时间, 分为BEFORE(检查约束前触发)和AFTER(检查约束后触发)
# trigger_event: 触发事件, INSERT, UPDATE 或 DELETE
DELIMITER $$
CREATE TRIGGER insert_user_trigger
AFTER INSERT ON user FOR EACH ROW
BEGIN
    insert into uuid_gen(uuid) values (REPLACE(UUID(),'-',''));
END
$$
DELIMITER ;

# 查看触发器
show triggers\G
# 删除触发器
drop trigger insert_user_trigger;

六、事务

1.普通事务

start transaction, commit, rollback, commit and chain; (自动开启一个新的事务)

# 开启事务
start transaction;
# 执行SQL
delete from product_order where id = 8;
# 此时查询时id=8的记录已删除
select * from product_order;
# 回滚事务
rollback;
# 回滚后可以查询到id=8的记录
select * from product_order;

2.分布式事务: XA事务

# 1.流程
# 1).启动XA事务
XA {START|BEGIN} xid [JOIN|RESUME]
# xid为XA事务标识符, 格式为 xid: gtrid[, bqual[, formatID]]
# gtrid: 分布式事务标识符, 同一个分布式事务应使用相同的gtrid
# bqual: 分支限定符, 一个分布式事务中的每个分支事务, 其bqual值必须唯一
# formatID: 用于标识gtrid和bqual值使用的格式, 默认值为1
# 2).执行业务SQL
# 3).XA事务完结
XA END xid
# 4).XA分支事务准备
XA PREPARE xid
# 5).XA分支事务详细信息查询(辅助功能)
XA RECOVER
# 6).提交XA分支事务
XA COMMIT xid
# 7).或者回滚分支事务
XA ROLLBACK xid

# 2.示例
# 1).启动一个分支事务, 产品库存减1, xid的gtrid为'order_tran', bqual为'product'
XA START 'order_tran'.'product';
UPDATE product SET stock = stock - 1 where id = 1;
XA END 'order_tran'.'product';
# 2).启动另一个分支事务, 插入订单
XA START 'order_tran'.'order';
INSERT INTO product_order(product_id, create_time) VALUES (1, NOW());
XA END 'order_tran'.'order';
# 3).'product'分支事务准备提交
XA PREPARE 'order_tran'.'product';
# 4).'order'分支事务准备提交
XA PREPARE 'order_tran'.'order';
# 5).提交所有分支事务
XA COMMIT 'order_tran'.'product';
XA COMMIT 'order_tran'.'order';